﻿using System;
using System.Data;
using System.Data.SqlClient;

namespace DexERP.Core.DBHelper
{
    public class MSSQLHelper
    {
        private SqlConnection Con { get; set; }
        private SqlCommand odbcmd { get; set; }
        private DataTable tmpdt { get; set; }
        public string database { get; set; }
        public string Host { get; set; }
        public string db_uid { get; set; }
        public string db_pwd { get; set; }
        public MSSQLHelper(string host, string uid, string pwd, string db)
        {
            Host = host;
            db_uid = uid;
            db_pwd = pwd;
            database = db;
            DBConnect();
        }

        /// <summary>
        /// 连接数据库，成功返回字符串success，失败返回错误信息
        /// </summary>
        /// <returns></returns>
        public string DBConnect()
        {
            Con = new System.Data.SqlClient.SqlConnection();
            string Constr = "Server=" + Host + ";Database=" + database + ";Uid=" + db_uid + ";Pwd=" + db_pwd + ";";
            return OpenDb(Constr);
        }

        private string OpenDb(string cnstr)
        {
            try
            {
                if ((Con.State == ConnectionState.Closed) || (Con.State == ConnectionState.Broken))
                {
                    Con.ConnectionString = cnstr;
                    Con.Open();
                }
                return "success";
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }

        public int ExecSql(string sql)
        {
            odbcmd = new SqlCommand();
            int t;
            try
            {
                odbcmd.Connection = Con;
                odbcmd.CommandText = sql;
                t = odbcmd.ExecuteNonQuery();
                return t;
            }
            catch
            {
                return 0;
            }
        }


        public void ConDispose()
        {
            if (Con.State == ConnectionState.Open)
            {
                Con.Close();
            }
            Con = null;
            tmpdt = null;
            odbcmd = null;
        }


        public DataTable Query(string sql)
        {
            SqlDataAdapter odbadp = new SqlDataAdapter();
            odbcmd = new SqlCommand();
            tmpdt = new DataTable();
            try
            {
                odbcmd.Connection = Con;
                odbcmd.CommandText = sql;
                odbadp.SelectCommand = odbcmd;
                odbadp.Fill(tmpdt);
                return tmpdt;
            }
            catch
            {
                return tmpdt;
            }
        }

    }
}
